Redshiftで分散スタイル＆分散キーごとの実行計画を比較してみた

Redshiftで分散スタイル＆分散キーごとの実行計画を比較してみた

#初心者向け
#Amazon Redshift
#AWS
みかみ

みかみ

facebook logohatena logotwitter logo
Clock Icon2016.12.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。三上です。

河原にどんと焼き（さいとう焼き？）ができてました。昔より小さく感じるのは、時代の流れでしょうか？（しみじみ。。

やりたいこと

  • Redshiftのテーブル設計を学びたい！（チューニングもできるようになりたい
  • 分散スタイルと分散キーの違いで何が変わるか確認したい

分散スタイルって・・・？

Redshiftの構成

aws_bb_rs_p7

  • Redshiftは、クラスタ ＞ ノード ＞ スライス　の構成
  • 各ノードは並列で動く
  • データは各スライスに分割して持つ
  • データが偏っちゃうと働かないノードがでてくるのでもったいない
  • 別のノードが持っているデータが必要になると、データを再分散しないといけないので時間がかかる

分散スタイルの種類

  • EVEN：均等分散
  • KEY：指定キーはまとめて同じスライスに分散
  • ALL：全ノード（先頭スライス）に同じテーブルをコピー

AWS Documentation 分散スタイル

確認してみよう

確認環境

以下のRedshift（1ノード2スライス）で確認しました。 rs_cluster_prop

確認データ

以下から、使いたいカラムデータのみ抽出したCSVを作成しました。

テーブル構成

下記テーブル構成で、populationテーブルにすべての分散スタイルを指定 → 実行計画を比較しました。

er_rs_dist_test

データ準備

Redshiftに以下のテーブルを作り、データをロードします。

create table m_addr(
    pref_cd varchar(255),
    city_cd varchar(255),
    pref varchar(255),
    city varchar(255),
    primary key(city_cd)
)diststyle even;

create table m_area_kind(
    kind varchar(255),
    name varchar(255),
    primary key(kind)
)diststyle even;

create table pop_even(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle even;

create table pop_dist(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle key distkey (area_cd);

create table pop_all(
    area_cd varchar(255),
    area_kind varchar(255),
    population int,
    width double precision,
    density double precision,
    house int,
    foreign key(area_cd) references m_addr(city_cd),
    foreign key(area_kind) references m_area_kind(kind)
)diststyle all;

分散スタイルを確認してみます。

select 
  relname, 
  reldiststyle 
from 
  pg_class 
where 
  relname like 'pop_%' 
  or relname = 'm_addr' 
  or relname = 'm_area_kind' 
order by 
  relname;

   relname   | reldiststyle
-------------+--------------
 m_addr      |            0
 m_area_kind |            0
 pop_all     |            8
 pop_dist    |            1
 pop_even    |            0
(5 rows)

※reldiststyleは以下の通りです。

  • 0：EVEN
  • 1：KEY
  • 8：ALL

AWS Documentation 分散スタイルの表示

※pop_distの分散キーは、area_cd を指定しました。

実際にデータが分散されているか確認してみます。

select
  name, slice, col, num_values as rows, minvalue, maxvalue
from
  svv_diskusage
where
  (name like 'pop_%' or name = 'm_addr' or name = 'm_area_kind') and col = 0 and rows > 0
order by
  name, slice, col;

                                   name                                   | slice | col | rows |   minvalue   |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+--------------+---------------------
 m_addr                                                                   |     0 |   0 | 2776 |            0 | 2314885530818459444
 m_addr                                                                   |     1 |   0 | 2777 |            0 | 2314885530818459444
 m_area_kind                                                              |     0 |   0 |    5 |           48 | 2314885530818453603
 m_area_kind                                                              |     1 |   0 |    4 |           49 | 2314885530818453604
 pop_all                                                                  |     0 |   0 | 5553 | 206966894640 | 3472328296237087727
 pop_dist                                                                 |     0 |   0 | 2757 | 211261861936 | 2314885604236801844
 pop_dist                                                                 |     1 |   0 | 2796 | 206966894640 | 3472328296237087727
 pop_even                                                                 |     0 |   0 | 2777 | 215556829232 | 3472328296237087727
 pop_even                                                                 |     1 |   0 | 2776 | 206966894640 | 2314885604236801844
(9 rows)

AWS Documentation 分散の例

rows の列を見ると、ALLの pop_all は先頭スライスに全データが格納され、それ以外は指定の分散スタイルで各スライスにデータが格納されました。

実行計画

実行計画のポイント

実行計画では

  • DS_DIST_NONE
  • DS_DIST_ALL_NONE

以外はダメ！（再分散されるから

※ひとまず、他は後回しで。。

実行計画をとる

各分散スタイルのテーブルに対する、以下のクエリの実行計画をとってみます。

※住所マスタ（m_addr）とJOINして、都道府県ごとの人口を取得するクエリです。

explain 
select 
  a.pref, 
  sum(p.population) as population 
from 
  [populationテーブル] p 
  inner join m_addr a on p.area_cd = a.city_cd 
group by 
  a.pref 
order by 
  population desc;

実行結果↓

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1189.06..1189.16 rows=39 width=14)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=69.41..1153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_all p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

→ALLのテーブルは DS_DIST_ALL_NONE なのでOK（その分ストレージ食ってますが。。）ですが、

EVENとKEYのテーブルに DS_BCAST_INNER が出てます。。

AWS Documentation クエリプランの評価

DS_BCAST_INNER 内部テーブル全体のコピーがすべてのコンピューティングノードにブロードキャストされます。

だそうで、よろしくありません。。

分散スタイルを見直す（１）

JOINのキー ＝ 分散キー ならば、再分散なしで済むはずなので、

JOINする住所マスタ（m_addr）の分散スタイルを変えてみることにします。

テーブルを作り直し、データを入れなおしました。

※分散キー（city_cd）＝ JOIN句の結合キー です。

drop table m_addr;

create table m_addr(
    pref_cd varchar(255),
    city_cd varchar(255),
    pref varchar(255),
    city varchar(255),
    primary key(city_cd)
)diststyle key distkey (city_cd);

defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_addr';
 relname | reldiststyle
---------+--------------
 m_addr  |            1
(1 row)


defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_addr' and col = 0 and rows > 0 order by name, slice, col;
                                   name                                   | slice | col | rows | minvalue |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
 m_addr                                                                   |     0 |   0 | 2757 |        0 | 2314885530818459444
 m_addr                                                                   |     1 |   0 | 2796 |        0 | 2314885530818459444
(2 rows)

再度、EVENとKEYのテーブルの実行計画を取ってみます。

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1332721189.06..1332721189.16 rows=39 width=14)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=69.41..1332721153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
         Send to leader
         ->  XN Sort  (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=1189.06..1189.16 rows=39 width=14)
                     ->  XN Hash Join DS_DIST_NONE  (cost=69.41..1153.65 rows=7082 width=14)
                           Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
                           ->  XN Seq Scan on m_addr a  (cost=0.00..55.53 rows=5553 width=19)
                           ->  XN Hash  (cost=55.53..55.53 rows=5553 width=13)
                                 ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=13)
(12 rows)

→KEYのテーブルが DS_DIST_NONE に変わりました！

分散スタイルを見直す（２）

もうひとつ、別のクエリの実行計画を取ってみます。

※地域種別マスタ（m_area_kind）とJOINして、地域種別ごとの人口を取得するクエリです。

explain
select
  k.name,
  sum(p.population) as population
from
  [populationテーブル] p 
  inner join m_area_kind k on p.area_kind = k.kind
group by
  k.name
order by
  population desc;

実行結果↓

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=3960208.35..3960208.37 rows=9 width=32)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=0.11..3960180.58 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=3960208.35..3960208.37 rows=9 width=32)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=0.11..3960180.58 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

先ほどと同じく、DS_BCAST_INNER 出てます。。

※同じ結果なので、ALLは省略しました。

分散キーは１つしか指定できないため、別のカラムをキーにしたJOINが必要になると、困ります。。

今度は、地域種別マスターの分散スタイルをALLに変更してみます。

drop table m_area_kind;

create table m_area_kind(
    kind varchar(255),
    name varchar(255),
    primary key(kind)
)diststyle all;

defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_area_kind';
   relname   | reldiststyle
-------------+--------------
 m_area_kind |            8
(1 row)


defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_area_kind' and col = 0 and rows > 0 order by name, slice, col;
                                   name                                   | slice | col | rows | minvalue |      maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
 m_area_kind                                                              |     0 |   0 |    9 |       48 | 2314885530818453604
(1 row)

実行計画を取ってみると・・・

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=208.35..208.37 rows=9 width=32)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..180.59 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_even p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
   Merge Key: sum(p.population)
   ->  XN Network  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
         Send to leader
         ->  XN Sort  (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
               Sort Key: sum(p.population)
               ->  XN HashAggregate  (cost=208.35..208.37 rows=9 width=32)
                     ->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..180.59 rows=5553 width=32)
                           Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
                           ->  XN Seq Scan on pop_dist p  (cost=0.00..55.53 rows=5553 width=9)
                           ->  XN Hash  (cost=0.09..0.09 rows=9 width=33)
                                 ->  XN Seq Scan on m_area_kind k  (cost=0.00..0.09 rows=9 width=33)
(12 rows)

→両方とも、DS_DIST_ALL_NONE になってくれました！（めでたしめでたしｖ

まとめ（わかったこと）

  • テーブルの分散スタイルは要検討（JOINが必要な場合は特に
  • スタイル指定しない場合のデフォルトはEVEN
  • スタイルはKEY(DISTKEY)指定がベスト KEY(DISTKEY)指定でパフォーマンスチューニングできる（2017/09/01 訂正）
  • JOINのキーを分散キーに指定するといい
  • カーディナリティの低い（バリエーションの少ない）カラムを分散キーに指定しちゃダメ（データが偏るから
  • テーブルサイズが小さい場合はALL指定でもOK
  • 分散スタイルでJOINに対応できない場合は、テーブルの非正規化も検討する

参照ドキュメント

Share this article

facebook logohatena logotwitter logo

関連記事

プログラミング経験ゼロの非エンジニアがAI-StarterでChrome拡張機能を作ってみた

プログラミング経験ゼロの非エンジニアがAI-StarterでChrome拡張機能を作ってみた

User avatar
のんべぇ
2024.11.19
[初学者向け] IAM 設計の観点解説とテンプレート化してみた

[初学者向け] IAM 設計の観点解説とテンプレート化してみた

EC2のユーザーデータに記述したスクリプトが実行されない

EC2のユーザーデータに記述したスクリプトが実行されない

User avatar
なかた
2024.08.29
Mackerel を触ってみる（ログ監視編）

Mackerel を触ってみる（ログ監視編）

User avatar
板倉舞
2024.08.28
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.